use SAS
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdRelationships')
	BEGIN
		DROP  Procedure  dbo.InsUpdRelationships
	END
GO

CREATE Procedure [dbo].[InsUpdRelationships] (
	@SOURCEGUID			VARCHAR(100)
,	@RELATIONSHIPTYPE	VARCHAR(10)
,	@PARTNERID			VARCHAR(10)
,	@ISMAIN				VARCHAR(10)	= '1'
,	@USERID				VARCHAR(10) = '1'
,	@RELATIONSHIPID		VARCHAR(10)	= '0'
-- BELOW PARAMETERS ARE CURRENTLY HARDCODED
)
AS
BEGIN

	--	RelationshipType	TypeDesc
	--	1	Reseller
	--	2	Contact
	--	3	Person Responsible
	--	4	Distributor
	--	5	Manager
	--	6	Child Account
	--	7	Account
	--	8	Activity Partner
	--	9	Task Partner


	SET @ISMAIN		= CASE WHEN @ISMAIN IN ('X','1') THEN '1' ELSE '' END


	IF EXISTS(SELECT 1 FROM TR_RELATIONSHIPS 
					WHERE CAST(SourceGUID AS VARCHAR(100)) = @SOURCEGUID 
					AND RelationShipType = @RelationShipType)
	BEGIN
		UPDATE TR_RELATIONSHIPS
		SET Partner2				= @PARTNERID
		,	IsMain					= @ISMAIN
		,	ChangedBy				= @USERID
		,	ChangeDate				= GETDATE()
		WHERE 
			CAST(SourceGUID AS VARCHAR(100)) = @SOURCEGUID 
		AND RelationShipType = @RELATIONSHIPTYPE	
	END
	ELSE
	BEGIN		-- INSERT 
		SELECT @RELATIONSHIPID = ISNULL(MAX(RELATIONSHIPID),0) + 1 FROM TR_RELATIONSHIPS WITH (NOLOCK)

		INSERT INTO TR_RELATIONSHIPS (RelationshipId, SourceGuid,Partner2,
									IsMain, RelationshipType, 
									CreatedBy, CreateDate, ChangedBy, ChangeDate)
		VALUES(
			@RELATIONSHIPID		--RelationshipId
		,	@SOURCEGUID			--SourceGuid
		,	@PartnerID			--Partner2
		,	@ISMAIN				--IsMain
		,	@RELATIONSHIPTYPE	--RelationshipType
		,	@USERID				--CreatedBy
		,	GETDATE()			--CreateDate
		,	@USERID				--ChangedBy
		,	GETDATE()			--ChangeDate
		)
	END


--	-- ALLOW ONLY 1 MAIN CONTACT FOR A GIVEN PARTNER2 ID
--	IF(@RELATIONSHIPTYPE = 2 AND @ISMAIN = '1')
--	BEGIN
--		UPDATE TR_RELATIONSHIPS
--		SET IsMain					= (CASE WHEN Partner2 = CAST(@PARTNERID AS INT) THEN '1' ELSE '0' END)				
--		,	ChangedBy				= @USERID
--		,	ChangeDate				= GETDATE()
--		WHERE 
--			CAST(SourceGUID AS VARCHAR(100)) = @SOURCEGUID
--		AND RelationShipType = @RELATIONSHIPTYPE
--	END
END
GO
